#!/usr/bin/env python
# -*- coding: utf-8 -*-
# piwadb 

import sys
import codecs
import sqlite3

from getpass import getpass

from xml.etree.ElementTree import *

import gdata.photos.service
import gdata.media
import gdata.geo

from piwacommon import *

ALBUM_TABLE_SQL=u"""
create table album (
        album_id text,
        album_title text,
        album_access text,
        album_pos text,
        album_link text,
        album_thumbnail text,
        primary key(album_id)
);
"""
PHOTO_TABLE_SQL=u"""
create table photo (
        album_id text,
        photo_id text,
        photo_title text,
        photo_pos text,
        photo_link text,
        photo_thumbnail text,
        primary key(photo_id)
);
"""
TAG_TABLE_SQL=u"""
create table tag (
        photo_id text,
        photo_tag text,
        primary key(photo_id, photo_tag)
);
"""
INSERT_ALBUM_SQL=u"insert into album values (?, ?, ?, ?, ?, ?)"
INSERT_PHOTO_SQL=u"insert into photo values (?, ?, ?, ?, ?, ?)"
INSERT_TAG_SQL=u"insert into tag values (?, ?)"

UPDATE_ALBUM_SQL=u"""
update album set album_title = ?,
                 album_access = ?,
                 album_pos = ?,
                 album_link = ?,
                 album_thumbnail = ?
where album_id = ?;
"""

UPDATE_PHOTO_SQL=u"""
update photo set album_id = ?,
                 photo_title = ?,
                 photo_pos = ?,
                 photo_link = ?,
                 photo_thumbnail = ?
where photo_id = ?;
"""

TAGMAP_SQL=u"select max(photo_tag) t, count(photo_tag) c from tag group by photo_tag order by c desc, t;"
ALBUM_LIST_SQL=u"select album_id, album_title, album_access, album_pos, album_thumbnail, album_link from album order by album_id;"
ALBUM_PUBLIC__LIST_SQL=u"""
select album_id,
        album_title,
        album_access,
        album_pos,
        album_thumbnail,
        album_link
from album
where album_access = 'public'
order by album_id;
"""
ALBUM_PROTECTED__LIST_SQL=u"""
select album_id,
        album_title,
        album_access,
        album_pos,
        album_thumbnail,
        album_link
from album
where album_access in ('public', 'protected')
order by album_id;
"""
PHOTO_LIST_SQL=u"select photo_id, photo_title, photo_pos, photo_thumbnail, photo_link from photo where album_id = ? order by photo_id;"
COUNT_PHOTO_SQL=u"select count(photo_id) cnt from photo where album_id = ?;"
TAG_LIST_SQL=u"select photo_tag from tag where photo_id = ? order by photo_tag;"

ALBUM_PHOTO_LIST_SQL=u"""
SELECT p.album_id album_id,
       a.album_title album_title,
       a.album_link album_link,
       a.album_thumbnail album_thumbnail,
       p.photo_id photo_id,
       p.photo_title photo_title,
       p.photo_link photo_link,
       p.photo_thumbnail photo_thumbnail
FROM album a INNER JOIN photo p on a.album_id = p.album_id
ORDER BY p.album_id, p.photo_id;
"""
QUERY_PHOTO_SQL=u"""
SELECT p.album_id album_id,
       a.album_title album_title,
       a.album_link album_link,
       a.album_thumbnail album_thumbnail,
       p.photo_id photo_id,
       p.photo_title photo_title,
       p.photo_link photo_link,
       p.photo_thumbnail photo_thumbnail
FROM album a INNER JOIN photo p on a.album_id = p.album_id
WHERE p.photo_id = ?
"""

COUNT_TAG_SQL=u"""
SELECT COUNT(photo_tag) cnt
FROM (SELECT DISTINCT photo_tag
      FROM tag)
"""


class PiwaDb :
    conn = None
    filename = None
    def __init__(self, filename=':memory:') :
        self.filename = filename

        try :
            self.conn = sqlite3.connect(self.filename)
        except sqlite3.OperationalError :
            self.conn.row_factory = sqlite3.Row
            self.conn.isolation_level = None        #auto commit mode
        else :
            self.conn.row_factory = sqlite3.Row
            self.conn.isolation_level = None        #auto commit mode
            try :
                self.conn.execute(ALBUM_TABLE_SQL)
                self.conn.execute(PHOTO_TABLE_SQL)
                self.conn.execute(TAG_TABLE_SQL)
            except sqlite3.OperationalError :
                pass

    def close(self) :
        self.conn.close()

    def __del__(self) :
        self.close()

    def addAlbum(self, album_id, album_title, album_access, album_pos, album_link, album_thumbnail) :
        try :
            self.conn.execute(INSERT_ALBUM_SQL, (album_id, album_title, album_access, album_pos, album_link, album_thumbnail))
        except sqlite3.IntegrityError :
            self.conn.execute(UPDATE_ALBUM_SQL, (album_title, album_access, album_pos, album_link, album_thumbnail, album_id))

    def addPhoto(self, album_id, photo_id, photo_title, photo_pos, photo_link, photo_thumbnail) :
        try :
            self.conn.execute(INSERT_PHOTO_SQL, (album_id, photo_id, photo_title, photo_pos, photo_link, photo_thumbnail))
        except sqlite3.IntegrityError :
            self.conn.execute(UPDATE_PHOTO_SQL, (album_id, photo_title, photo_pos, photo_link, photo_thumbnail, photo_id))

    def addTag(self, photo_id, photo_tag) :
        try :
            self.conn.execute(INSERT_TAG_SQL, (photo_id, photo_tag))
        except sqlite3.IntegrityError :
            pass        # tag table only primary key. if already exists then no update.

    def tagmap(self) :
        map = {}
        index = 0
        for row in self.conn.execute(TAGMAP_SQL) :
            map[row['t']] = index
            index += 1
        return map

    def photoCount(self, album_id) :
        cnt = 0
        for row in self.conn.execute(COUNT_PHOTO_SQL, (album_id,)):
            cnt = row['cnt']
            break
        return cnt

    def tagCount(self) :
        cnt = 0
        for row in self.conn.execute(COUNT_TAG_SQL) :
            cnt = row['cnt']
            break
        return cnt

    def albumList(self, access='all') :
        if access == 'public':
            return self.conn.execute(ALBUM_PUBLIC_LIST_SQL)
        elif access == 'protected':
            return self.conn.execute(ALBUM_PROTECTED_LIST_SQL)
        else:
            return self.conn.execute(ALBUM_LIST_SQL)

    def photoList(self, album_id) :
        return self.conn.execute(PHOTO_LIST_SQL, (album_id,))   # one element tupl...

    def tagList(self, photo_id) :
        return self.conn.execute(TAG_LIST_SQL, (photo_id,))   # one element tupl...

    def albumPhotoList(self) :
        return self.conn.execute(ALBUM_PHOTO_LIST_SQL)

    def queryPhotoInfo(self, photo_id) :
        return self.conn.execute(QUERY_PHOTO_SQL, (photo_id,))   # one element tupl...

    def fromWeb(self, gd_client, owner, access='all', console_encode = None, album_only = False) :
        albums = gd_client.GetUserFeed(user=owner)
        for album in albums.entry:
            album_id = uni(album.gphoto_id.text, GDATA_ENCODE)
            album_access = uni(album.access.text, GDATA_ENCODE)

            #checking access.... public -> only 'public', protected -> 'public' and 'protected'
            if invalidAccess(access, album_access) :
                continue
    
            album_title = uni(album.title.text, GDATA_ENCODE)
            album_pos = uni(album.geo.Point.pos.text, GDATA_ENCODE)
            album_link = uni(album.GetHtmlLink().href, GDATA_ENCODE)
            album_thumbnail = uni(album.media.thumbnail[0].url, GDATA_ENCODE)
            self.addAlbum(album_id, album_title, album_access, album_pos, album_link, album_thumbnail)

            if console_encode != None :
                sys.stderr.write((u'album %s access:%s geo:%s\n'%(album_title, album_access, album_pos)).encode(console_encode))

            if album_only != True:    
                photos = gd_client.GetFeed(
                    '/data/feed/api/user/%s/albumid/%s?kind=photo' % (
                        owner, album.gphoto_id.text))
                for photo in photos.entry:
                    photo_id = uni(photo.id.text, GDATA_ENCODE)
                    photo_title = uni(photo.title.text, GDATA_ENCODE)
                    photo_pos = uni(photo.geo.Point.pos.text, GDATA_ENCODE)
                    photo_link = uni(photo.GetHtmlLink().href, GDATA_ENCODE)
                    photo_thumbnail = uni(photo.media.thumbnail[0].url, GDATA_ENCODE)

                    self.addPhoto(album_id, photo_id, photo_title, photo_pos, photo_link, photo_thumbnail)
                    if console_encode != None :
                        sys.stderr.write((u'\tphoto %s geo:%s\n' % (photo_title, photo_pos)).encode(console_encode))

                    #tag
                    photo_tags = gd_client.GetFeed('/data/feed/api/user/%s/albumid/%s/photoid/%s?kind=tag'
                                                   % (owner, album.gphoto_id.text, photo.gphoto_id.text))
                    for photo_tags_entry in photo_tags.entry:
                        #print "\t\tPhoto tag", photo_tags_entry.title.text
                        photo_tag = u''
                        if photo_tags_entry.title.text != None :
                            photo_tag = unicode(photo_tags_entry.title.text, TAG_ENCODE)
                            self.addTag(photo_id, photo_tag)
                            if console_encode != None:
                                sys.stderr.write((u'\t\ttag %s\n'%photo_tag).encode(console_encode))


#test... see test_piwadb.py
